# -*- coding: utf-8 -*-

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class Create03PsqlData(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_col(self):
        """
        定义psql 插入字段类型
        :return:
        """
        id = self.get_incre_time()
        col = {
            "id": "serial,",
            "c_02_integer_col": "integer,",
            "c_03_smallint_col": "smallint,",
            "c_04_bigint_col": "bigint,",
            "c_05_double_col": "numeric(9,2),",
            "c_06_float_col": "numeric(9,2),",
            "c_07_decimal_col": "numeric(7,2),",
            "c_08_date_col": "date,",
            "c_09_time_col": "time,",
            "c_10_timestamp_col": "timestamp,",
            "c_11_char_col": "varchar(50)",
            "": ")"
        }
        return col

    def define_ddl(self, db, schemaName, tableName):
        """
        拼接oracle ddl语句
        :param table:
        :return:
        """
        col = self.define_col()
        ddl_sql = 'CREATE TABLE {}.{} ( \r\n'.format(schemaName, tableName)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def generate_dml_sql(self, databaseName, schemaName, tableName, num):
        """
        批量生成 PostgreSQL DML 语句
        :param database: 数据库名称
        :param table: 表名称
        :param num: 生成的记录数量
        :return: 插入的 SQL 语句
        """
        insert_sql = "INSERT INTO {}.{} (id, c_02_integer_col, c_03_smallint_col, c_04_bigint_col, c_05_double_col, c_06_float_col," \
                     " c_07_decimal_col, c_08_date_col, c_09_time_col, c_10_timestamp_col, c_11_char_col) VALUES ".format(
            schemaName, tableName)

        res_num = self.get_count(databaseName, schemaName, tableName)  # 获取当前表已有的记录数

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            c_02_integer_val = self.get_random(-2147483648, 2147483647)
            c_03_smallint_val = self.get_random(-32768, 32767)
            c_04_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
            c_05_double_val = self.get_random_float(-500, 500)
            c_06_float_val = self.get_random_float(-500, 500)
            c_07_decimal_val = self.get_random_float(-32768, 32767)
            c_08_date_val = "TO_DATE('{}', 'YYYY-MM-DD')".format(self.get_random_date())
            c_09_time_val = "'{}'".format(self.get_random_time())
            c_10_timestamp_val = "TO_TIMESTAMP('{}', 'YYYY-MM-DD HH24:MI:SS')".format(
                self.get_random_datetime())
            c_11_char_val = "'{}'".format(self.get_random_string(30))

            values_list.append("({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                i,
                c_02_integer_val,
                c_03_smallint_val,
                c_04_bigint_val,
                c_05_double_val,
                c_06_float_val,
                c_07_decimal_val,
                c_08_date_val,
                c_09_time_val,
                c_10_timestamp_val,
                c_11_char_val
            ))

        insert_sql += ", ".join(values_list) + ";"

        return insert_sql

    def set_combkey(self, db, table):
        line = "SELECT column_name FROM information_schema.columns WHERE table_name = {} ORDER BY ordinal_position LIMIT 1".format(
            table)
        print(line)
        line = "show server_version"
        columns = self.exec_psql_sql(db, line)
        print(columns)
        column1 = columns[0][0]
        column2 = columns[1][0]
        print(column1, column2)

    def set_slot(self, db, slot):
        line = "SELECT pg_drop_replication_slot('{}')".format(slot)
        self.exec_psql_sql(db, line)

    def get_count(self, databaseName, schemaName, tableName):
        sql = "select count(1) as cot from {}.{}.{}".format(databaseName, schemaName, tableName)
        res = self.exec_psql_sql(databaseName, sql)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return None

    def execute_drop_table(self, databaseName, schemaName, tableName):
        sql = "drop table {}.{}.{} CASCADE".format(databaseName, schemaName, tableName)
        res = self.exec_psql_sql(databaseName, sql)
        return self.set_return_massage(res)

    def cdc_set_on(self, db, schemaName, table):
        sql = "alter TABLE {}.{} REPLICA IDENTITY FULL".format(schemaName, table)
        return self.exec_psql_sql(db, sql)

    def execute_deleted_data(self, databaseName, schemaName, tableName):
        sql = ("delete FROM {}.{}.{} WHERE id IN (SELECT id FROM (SELECT id FROM {}.{}.{} ORDER BY id DESC LIMIT 100)"
               " AS subquery);".format(databaseName, schemaName, tableName, databaseName, schemaName, tableName))
        res = self.exec_psql_sql(databaseName, sql)
        if res['message'] == "Success":
            return True
        else:
            return False

    def execute_update_data(self, databaseName, schemaName, tableName):
        sql1 = (("select attname AS column_name, atttypid::regtype AS data_type, attnotnull AS is_not_null FROM "
                 "pg_attribute WHERE attrelid = '{}.{}.{}'::regclass AND attnum > 0 AND NOT attisdropped;").
                format(databaseName, schemaName, tableName))

        res = self.exec_psql_sql(databaseName, sql1)
        if res['message'] != "Success":
            return False

        columns = [row[0] for row in res['data'] if row[0] != 'id']

        get_ids_sql = "select id FROM {}.{}.{} ORDER BY id DESC LIMIT 10".format(databaseName, schemaName, tableName)
        ids_res = self.exec_psql_sql(databaseName, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]

        for id in ids:
            updates = []
            for col in columns:
                if col == 'c_02_integer_col':
                    updates.append("{} = {}".format(col, self.get_random(-2147483648, 2147483647)))
                elif col == 'c_03_smallint_col':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'c_04_bigint_col':
                    updates.append("{} = {}".format(col, self.get_random(-9223372036854775808, 9223372036854775807)))
                elif col == 'c_05_double_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_06_float_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_07_decimal_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-32768, 32767)))
                elif col == 'c_08_date_col':
                    updates.append("{} = TO_DATE('{}', 'YYYY-MM-DD')".format(col, self.get_random_date()))
                elif col == 'c_09_time_col':
                    updates.append("{} = '{}'".format(col, self.get_random_time()))
                elif col == 'c_10_timestamp_col':
                    updates.append(
                        "{} = TO_TIMESTAMP('{}', 'YYYY-MM-DD HH24:MI:SS')".format(col, self.get_random_datetime()))
                elif col == 'c_11_char_col':
                    updates.append("{} = '{}'".format(col, self.get_random_string(30)))

            update_sql = "update {}.{}.{} SET {} WHERE id = {}".format(databaseName, schemaName, tableName, ", ".
                                                                       join(updates), id)
            update_res = self.exec_psql_sql(databaseName, update_sql)
            if update_res['message'] != "Success":
                return False

        return True

    def get_db_data(self, databaseName, schemaName, tableName):
        sql = "select * FROM {}.{}.{} ORDER BY id DESC LIMIT 10".format(databaseName, schemaName, tableName)
        res = self.exec_psql_sql(databaseName, sql)
        return self.set_return_massage(res)


if __name__ == '__main__':
    a = Create03PsqlData()
    asd = a.get_db_data("qa_source", "public", "muti_psql_table1")
    print(asd)
    # a.create_and_insert_sql("qa_source", "cdc_psql_relase_reader", 100, None)
    # a.get_count("qa_source", "public.cdc_psql_relase_reader")
    # a.cdc_set_on("qa_source", "public.aaa1")
    # un = DBUntilsTools()
    # asd = "TO_TIMESTAMP({}, 'YYYY-MM-DD HH24:MI:SS')".format(un.get_random_datetime())
    # print(asd)
    # a.set_combkey("seatunnel")
